﻿<SQLStoredProcedureSpec
	xmlns="bb_appfx_sqlstoredprocedure"
	xmlns:common="bb_appfx_commontypes" 
	ID="6b02bcab-091f-4696-8e51-5eccd517989c"
	Name="TDSM Organizational Hierarchy"
	Description="Gets the organizational hierarchy"
	Author="Blackbaud Professional Services"
	SPName="USR_USP_TDSM_ORGANIZATIONS"
	>

	<CreateProcedureSQL>
		<![CDATA[
create procedure dbo.USR_USP_TDSM_ORGANIZATIONS
as
begin

SELECT  
  ORGP.NAME POSITION
 ,CON.NAME POSITIONHOLDER
 ,BUC.DESCRIPTION AS BUSINESSUNITNAME
 ,ISNULL(UBUC.THRESHOLD,0) AS MINIMUMTHRESHOLD
 ,CON.ID AS POSITIONHOLDERCONSTITUENTID
 ,BUC.ID AS BUSINESSUNITCODEID
 ,ORG.ID AS ORGANIZATIONHIERARCHYID
 ,ORGP.ID AS ORGANIZATIONPOSITIONID
 ,ORGPH.ID AS ORGANIZATIONPOSITIONHOLDERID
FROM
  ORGANIZATIONHIERARCHY ORG
  INNER JOIN
  ORGANIZATIONPOSITION ORGP
  ON ORGP.ID = ORG.ID  
  INNER JOIN
  ORGANIZATIONPOSITIONHOLDER ORGPH
  ON ORGPH.POSITIONID = ORGP.ID
  INNER JOIN
  CONSTITUENT CON
  ON CON.ID = ORGPH.CONSTITUENTID
  INNER JOIN
  BUSINESSUNITCODE BUC
  ON BUC.ID = ORGP.BUSINESSUNITCODEID
  INNER JOIN
  USR_BUSINESSUNITCODE UBUC
  ON UBUC.ID = BUC.ID;
end;
]]>
	</CreateProcedureSQL>

</SQLStoredProcedureSpec>
